
DROP TABLE INTEX.QVW_KDSTAMM
GO
CREATE TABLE QVW_KDSTAMM
AS
SELECT DISTINCT KDMANDANT AS MANDANT,KDKUNDNR AS KDNR,KDKUNDNA AS KDSNAME,KDNAME1 AS KDNAME,KDLAND,KDORT
FROM KDSTAMM WHERE KDMANDANT='00'
GO
DROP TABLE INTEX.QVW_GSDAT
GO
CREATE TABLE QVW_GSDAT
AS
SELECT DISTINCT GSMANDANT AS MANDANT,'Y' AS ARTST,GSART AS PART,GSVAR AS PVAR,GSART||GSVAR AS PFART,GSHANDLNR, GSGARNBEZ AS PARTBEZ,GSMAT,
GSNUMART AS GNUM,GSGARNNR AS GCOUNT,GSFACH AS GPLY,
GSPROGRP AS PRGR,GSARTGRP AS ARTGRP
FROM GSDAT WHERE GSMANDANT='00'
GO
DROP TABLE INTEX.QVW_FWASTAMM
GO
CREATE TABLE QVW_FWASTAMM
AS
SELECT DISTINCT WAMANDANT AS MANDANT,'F' AS ARTST, WAART AS PART,WAVAR AS PVAR, WAART||WAVAR AS PFART,WABEZ AS PARTBEZ,
WAFDDIM AS DENU,WAWAART,WAFGRQM AS GRQM, WAFGRLFD AS RLFD,WAFBRTU as WIDTH,WAFFDDIK AS WDEN,WAFFDDIS AS FDEN,
WAPROGRP AS PRGR,WAARTGRP AS ARTGRP
FROM WASTAMM WHERE WAMANDANT='00'
GO

DROP TABLE INTEX.QVW_GWASTAMM
GO
CREATE TABLE QVW_GWASTAMM
AS
SELECT DISTINCT WAMANDANT AS MANDANT,'R' AS ARTST, WAVWART AS PART,WAVWVAR AS PVAR, WAVWART||WAVWVAR AS PFART,WABEZ AS PARTBEZ,
WAFDDIM AS DENU,WAWAART,WARGRQM AS GRQM, WARGRLFD AS RLFD,WARWBRT as WIDTH,WARFDDIK AS WDEN,WARFDDIS AS FDEN,
WAPROGRP AS PRGR,WAARTGRP AS ARTGRP
FROM WASTAMM WHERE WAMANDANT='00' AND SUBSTR(WAVWART,1,2)<>'  '
GO

DROP TABLE INTEX.QVW_BHSATZ
GO
CREATE TABLE QVW_BHSATZ
AS
SELECT DISTINCT BHMANDANT AS MANDANT, 'K' AS ARTST,BHKART AS PART,BHKVAR AS PVAR, BHKART||BHKVAR AS PFART,
BSBEZ AS PARTBEZ,BSFDZ
FROM BHSATZ JOIN BSSATZ ON BSMANDANT=BHMANDANT AND BSKART=BHKART
WHERE BHMANDANT='00'
GO
DROP TABLE INTEX.QVW_FSSTAMM
GO
CREATE TABLE QVW_FSSTAMM
AS
SELECT DISTINCT FSMANDANT AS MANDANT,'T' AS ARTST, FSART AS PART,FSVAR AS PVAR, FSART||FSVAR AS PFART,FSABEZ AS PARTBEZ,
FSMATGRP AS MATGRP,
FSPROGRP AS PRGR,FSARTGRP AS ARTGRP
FROM FSSTAMM WHERE FSMANDANT='00'
GO

DROP TABLE INTEX.QVW_MSSTAMM
GO
CREATE TABLE QVW_MSSTAMM
AS
SELECT DISTINCT MSMANDANT AS MANDANT,'M' AS ARTST, MSMAT AS PART,MSVAR AS PVAR, MSMAT||MSVAR AS PFART,MSBEZ AS PARTBEZ
FROM MSSTAMM WHERE MSMANDANT='00'
GO

DROP TABLE INTEX.QVW_ABPOS
GO
CREATE TABLE QVW_ABPOS
AS
SELECT DISTINCT ABPMANDANT AS MANDANT,'V'||ABPAUFKRS||ABPAUFLFD AS KAUF,'V' AS AUFTYP,
'V'||ABPAUFKRS||ABPAUFLFD||ABPAUFPOS AS PAUF,
ABPAUFKRS,ABPAUFLFD,ABPAUFPOS,
ABPWAKZ AS ARTST,ABPART AS PART,ABPVAR AS PVAR,
ABPART||ABPVAR AS PFART,
ABPART||ABPVAR||ABPFARBE AS PFCOLOR,
ABPART||ABPVAR||ABPDESS||ABPCOLORIT AS PFPRINT,
ABPART||ABPVAR||ABPBESCHICH AS PFFINSH,
ABPART||ABPVAR||ABPFARBE||ABPGROE AS PFSKU,
ABPLIEFNR AS PLIEFNR,
ABPSOLLMENGE1*0.01 AS PQTY,
ABPSOLLMENGE1*0.01 AS PQTY1,
ABPSOLLMENGE2*0.01 AS PQTY2,
ABPSOLLMENGE3*0.01 AS PQTY3,
ABPSOLLMENGE4*0.01 AS PQTY4,
ABPSOLLMENGE5*0.01 AS PQTY5,
ABPSOLLMENGE6*0.01 AS PQTY6,
ABPSOLLMENGE7*0.01 AS PQTY7,
ABPSOLLMENGE8*0.01 AS PQTY8,
ABPLIEFDAT,
SUBSTR(ABPLIEFDAT,1,4) AS DYEAR,
SUBSTR(ABPLIEFDAT,5,2) AS DMONTH,
SUBSTR(ABPLIEFDAT,7,2) AS DDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(ABPLIEFDAT,5,2))/3) AS DQUARTER,
ABPSTARTDAT,
SUBSTR(ABPSTARTDAT,1,4) AS BYEAR,
SUBSTR(ABPSTARTDAT,5,2) AS BMONTH,
SUBSTR(ABPSTARTDAT,7,2) AS BDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(ABPSTARTDAT,5,2))/3) AS BQUARTER,
ABPENDDAT,
SUBSTR(ABPENDDAT,1,4) AS EYEAR,
SUBSTR(ABPENDDAT,5,2) AS EMONTH,
SUBSTR(ABPENDDAT,7,2) AS EDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(ABPENDDAT,5,2))/3) AS EQUARTER,
ABPANRKR||ABPALFDNR AS PFLOW,
ABPGESSTA AS PSTATUS,
ABPSPGRD AS PSTATUSCODE,
ABPWSSTA,ABPAPSTA,ABPRZSTA,ABPWPSTA,ABPWBSTA,ABPABSTA,
ABPERLKZ AS PFEND,ABPERLDAT AS FENDDAT,
SUBSTR(ABPERLDAT,1,4) AS FYEAR,
SUBSTR(ABPERLDAT,5,2) AS FMONTH,
SUBSTR(ABPERLDAT,7,2) AS FDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(ABPERLDAT,5,2))/3) AS FQUARTER
FROM ABPOS WHERE ABPMANDANT='00'
GO
DROP TABLE INTEX.QVW_WBBST
GO
CREATE TABLE QVW_WBBST
AS
SELECT DISTINCT WBBMANDANT AS MANDANT,WBBWAKZ AS ARTST, WBBART AS PART,WBBVAR AS PVAR,
WBBART||WBBVAR AS PFART,
WBBART||WBBVAR||WBBFARBE AS PFCOLOR,
WBBART||WBBVAR||WBBDESS||WBBCOLORIT AS PFPRINT,
WBBART||WBBVAR||WBBBESCHICH AS PFFINSH,
WBBLO AS LO,WBBLB AS LB,
SUM(WBBISTKLG*0.001) AS STLENG,
SUM(WBBTSTKLG*0.001) AS STLLENG,
SUM(WBBSGEW*0.001) AS STWEIGHT,
WBBAUFTYP AS AUFTYP,
WBBAUFTYP||WBBAUFKRS||WBBAUFLFD AS KAUF,
WBBAUFTYP||WBBAUFKRS||WBBAUFLFD||WBBAUFPOS AS PAUF
FROM WBBST WHERE WBBMANDANT='00'
GROUP BY WBBMANDANT,WBBLO,WBBLB,WBBWAKZ,WBBART,WBBVAR,WBBFARBE,WBBDESS,WBBCOLORIT,WBBBESCHICH,WBBAUFTYP,WBBAUFKRS,WBBAUFLFD,WBBAUFPOS
GO
DROP TABLE INTEX.QVW_BABST
GO
CREATE TABLE QVW_BABST
AS
SELECT DISTINCT BMMANDANT AS MANDANT,'K' AS ARTST, BMKART AS PART,BMKVAR AS PVAR,
BMKART||BMKVAR AS PFART,
BMKART||BMKVAR||BMFARBE AS PFCOLOR,
BMLO AS LO,BMLB AS LB,
SUM(BMSOLLLG*0.001) AS STLENG,
BMAUFTYP AS AUFTYP,
BMAUFTYP||BMAUFKRS||BMAUFLFD AS KAUF,
BMAUFTYP||BMAUFKRS||BMAUFLFD||BMAUFPOS AS PAUF
FROM BABST WHERE BMMANDANT='00'
GROUP BY BMMANDANT,BMLO,BMLB,BMKART,BMKVAR,BMFARBE,BMAUFTYP,BMAUFKRS,BMAUFLFD,BMAUFPOS
GO
DROP TABLE INTEX.QVW_GLLG
GO
CREATE TABLE QVW_GLLG
AS
SELECT DISTINCT GLMANDANT AS MANDANT,'Y' AS ARTST, GPBART AS PART,GPBVAR AS PVAR,
GPBART||GPBVAR AS PFART,
GPBART||GPBVAR||GPBFARBE AS PFCOLOR,
GLLO AS LO,GLLB AS LB,
SUM(GLBSTKG*0.001) AS STWEIGHT

FROM GLLG JOIN GPBST ON GLPARTIE=GPBPARTIE
 WHERE GLMANDANT='00'
GROUP BY GLMANDANT,GLLO,GLLB,GPBART,GPBVAR,GPBFARBE
GO
DROP TABLE INTEX.QVW_VAART
GO
CREATE TABLE QVW_VAART
AS
SELECT VAAMANDANT AS MANDANT,VAAKOLL||VAAART AS VART,VAABEZ AS VARTBEZ,VAAARTST AS ARTST,VAAPRGR AS PRGR,VAAARTGRP AS ARTGRP,
VAADART||VAADVAR AS PFART
FROM VAART WHERE VAAMANDANT='00'
GO
DROP TABLE INTEX.QVW_VAFARBEN
GO
CREATE TABLE QVW_VAFARBEN
AS
SELECT DISTINCT VAFMANDANT AS MANDANT,VAFKOLL,VAFART,VAFFARBE,VAFPGROE,
VAFKOLL||VAFART AS VART,
VAFKOLL||VAFART||VAFFARBE AS VFARBE,
VAFKOLL||VAFART||VAFFARBE||VAFPGROE AS VASKU,
VAFBEZ AS VFBEZ,
VAFPART||VAFPVAR AS PFART,
ROUND( MAX(VKPPREIS*0.001*VKKKURS*0.0001/(VKPMEEHFKT*0.000001))* dbms_random.value(0.7,1.5),2) AS PFCOST,
VAFPART||VAFPVAR||VAFFFARBE AS PFCOLOR,
VAFPART||VAFPVAR||VAFDESS||VAFCOLORIT AS PFPRINT,
VAFPART||VAFPVAR||VAFBESCHICH AS PFFINSH,
VAFPART||VAFPVAR||VAFFFARBE||VAFPGROE AS PFSKU
FROM VAFARBEN F
JOIN VKPOS P ON  VAFKOLL=VKPKOLL AND   VKPART=VAFART AND VKPFARBE=VAFFARBE
JOIN VKKOPF ON VKKAUFKRS=VKPAUFKRS AND VKKAUFLFD=VKPAUFLFD AND VKKMANDANT=VKPMANDANT
WHERE VAFMANDANT='00' AND VKKSA='  ' AND VKPSA='  '
AND VKPMEEHFKT<>'000000000000'
GROUP BY VAFMANDANT,VAFKOLL,VAFART,VAFFARBE,VAFPGROE,VAFBEZ,VAFPART,VAFPVAR,VAFDESS,VAFCOLORIT,VAFBESCHICH,VAFFFARBE

GO

DROP TABLE INTEX.QVW_VKPOS
GO

CREATE TABLE QVW_VKPOS
AS
SELECT DISTINCT
VKPMANDANT AS MANDANT,
VKKVB,VKKVG,
SUBSTR(VKPNEUDAT,1,4) AS NYEAR,
SUBSTR(VKPNEUDAT,5,2) AS NMONTH,
SUBSTR(VKPNEUDAT,7,2) AS NDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(VKPNEUDAT,5,2))/3) AS NQUARTER,
 'K'||VKPAUFKRS||VKPAUFLFD AS KAUF,'K' AS AUFTYP,'K'||VKPAUFKRS||VKPAUFLFD||VKPAUFPOS AS PAUF
 ,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,VKPNEUDAT,VKPNEUBEN,VKPKUNDNR AS KDNR,VKPSACHBE, VKPPOSTYP,
VKPARTST AS ARTST,VKPKOLL,VKPART,VKPFARBE,VKPGROE,VKPKOLL||VKPART AS VART,
VKPKOLL||VKPART||VKPFARBE AS VFARBE,
VKPKOLL||VKPART||VKPFARBE||VKPGROE AS VASKU,
VKPPRGR AS PRGR,VKPARTGRP AS ARTGRP,
VKPMEEH,VKPPREH,
ROUND( VKPPREIS*0.001*VKKKURS*0.0001/(VKPMEEHFKT*0.000001),2)  AS VKPPREIS,
 PFCOST,
ROUND(VKPPREIS*0.001*VKKKURS*0.0001/(VKPMEEHFKT*0.000001)-PFCOST,2) AS GPROFIT,
VKKKURS*0.0001 AS EXCHANGERATE,
ROUND(VKPMENGE1*0.01*VKPMEEHFKT*0.000001*VKPPREIS*0.001*VKKKURS*0.0001,2)  AS VKPVALUE,
ROUND(VKPMENGE1*0.01*VKPMEEHFKT*0.000001,2) AS VKPQTY,
ROUND(VKPRESERV*0.01*VKPMEEHFKT*0.000001,2) AS VKPRESERV,
ROUND(VKPZUGET*0.01*VKPMEEHFKT*0.000001,2) AS VKPZUGET,
ROUND(VKPFREIGE*0.01*VKPMEEHFKT*0.000001,2) AS VKPFREIGE,
ROUND(VKPKOMMISS*0.01*VKPMEEHFKT*0.000001,2) AS VKPKOMMISS,
ROUND(VKPVERS*0.01*VKPMEEHFKT*0.000001,2) AS VKPVERS,
ROUND(VKPFAKT*0.01*VKPMEEHFKT*0.000001,2) AS VKPFAKT,
SUBSTR(VKPWUTERM,1,4) AS SYEAR,
SUBSTR(VKPWUTERM,5,2) AS SMONTH,
SUBSTR(VKPWUTERM,7,2) AS SDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(VKPWUTERM,5,2))/3) AS SQUARTER,
SUBSTR(VKPLIEFTERM,1,4) AS DYEAR,
SUBSTR(VKPLIEFTERM,5,2) AS DMONTH,
SUBSTR(VKPLIEFTERM,7,2) AS DDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(VKPLIEFTERM,5,2))/3) AS DQUARTER,
SUBSTR(VKPFREITERM,1,4) AS FYEAR,
SUBSTR(VKPFREITERM,5,2) AS FMONTH,
SUBSTR(VKPFREITERM,7,2) AS FDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(VKPFREITERM,5,2))/3) AS FQUARTER,
SUBSTR(VKPLVDAT,1,4) AS VYEAR,
SUBSTR(VKPLVDAT,5,2) AS VMONTH,
SUBSTR(VKPLVDAT,7,2) AS VDAY,
'Q'||CEIL(TO_NUMBER(SUBSTR(VKPLVDAT,5,2))/3) AS VQUARTER,
VKPWVTERM,VKPWUTERM,VKPFREITERM,VKPVERSTERM,VKPLIEFTERM
FROM VKPOS JOIN VKKOPF ON VKPMANDANT=VKKMANDANT AND VKPAUFKRS=VKKAUFKRS AND VKPAUFLFD=VKKAUFLFD
JOIN QVW_VAFARBEN F ON VKPMANDANT=F.MANDANT AND VKPKOLL=VAFKOLL AND VKPART=F.VAFART AND VKPFARBE=VAFFARBE AND VKPGROE=VAFPGROE
 WHERE VKPMANDANT='00' AND VKKSA='  'AND  VKPSA='  '   AND VKPMEEHFKT<>'000000000000'
GO

 SELECT * FROM QVW_VKPOS
